package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import model.Dept;
import model.Power;
import model.Role;
import model.User;
import util.DBUtil;
import util.Page;
import dao.UserDAO;

public class UserDAOImpl implements UserDAO {
	
	private DBUtil dbu = new DBUtil();
	private Connection conn = null;
	private PreparedStatement pstat = null;
	private ResultSet rs = null;
	RoleDAOImpl roledaoimpl=new RoleDAOImpl();
	PowerDAOImpl powerdaoimpl=new PowerDAOImpl();
	
	public boolean addUser(User user) {
		boolean flag=false;
		try{
			conn = dbu.getConnection();
			String sql="insert into t_user(username,password,roleid,status,powerid) values(?,?,?,?,?)";
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, user.getUsername());
			pstat.setString(2, user.getPassword());
			pstat.setInt(3, user.getRole().getId());
			if(user.isStatus()){
				pstat.setString(4, "T");
			}else{
				pstat.setString(4, "F");
			}
			pstat.setInt(5, user.getPower().getId());
			pstat.executeUpdate();
			flag=true;
		}catch (Exception e) {
			System.out.println("添加用户，连接数据库出错!!");
		}finally{
			dbu.closeConnection(conn, pstat, rs);
		}
		return flag;
	}

	public boolean deleteByUserName(String name) {
		boolean flag=false;
		try{
			conn = dbu.getConnection();
			String sql="delete from t_user where username=?";
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, name);
			pstat.executeUpdate();
			flag=true;
		}catch (Exception e) {
			System.out.println("删除用户，连接数据库出错!!");
		}finally{
			dbu.closeConnection(conn, pstat, rs);
		}
		return flag;
	}

	public Page<User> getAll(int pageNo, int pageSize) {
		Page<User> page = new Page<User>(pageNo, pageSize);
		int totalCount = 0;
		List<User> list = new ArrayList<User>();
		try {
			conn = dbu.getConnection();
			String sql = "select count(*) from t_user";
			pstat = conn.prepareStatement(sql);
			rs = pstat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select u.*,rownum rn from t_user u where rownum<=? ) where rn>=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, page.getPageNo() * pageSize);
			pstat.setInt(2, (page.getPageNo() - 1) * pageSize + 1);
			rs = pstat.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setUsername(rs.getString("USERNAME"));
				user.setPassword(rs.getString("PASSWORD"));
				Role role=new Role();
				role=roledaoimpl.getById(rs.getInt("ROLEID"));
				user.setRole(role);
				if("T".equals(rs.getString("STATUS"))){
					user.setStatus(true);
				}else{
					user.setStatus(false);
				}
				Power power=new Power();
				power=powerdaoimpl.getPowerById(rs.getInt("POWERID"));
				user.setPower(power);
				list.add(user);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return page;
	}

	public User getById(String name) {
		User user=new User();
		Role role=new Role();
		Power power=new Power();
		String sql="select t.username,t.password,t.status,t.roleid,r.name,t.powerid,p.name from t_user t,role r,t_power p where username='"+name+"' and t.powerid=p.id and t.roleid=r.id";
		try{
			conn=dbu.getConnection();
			pstat=conn.prepareStatement(sql);
			rs=pstat.executeQuery();
			if(rs.next()){
				role.setId(rs.getInt("ROLEID"));
				role.setName(rs.getString("NAME"));
				power.setId(rs.getInt("POWERID"));
				power.setName(rs.getString("NAME"));
				user.setUsername(rs.getString("USERNAME"));
				user.setPassword(rs.getString("PASSWORD"));
				String tf=rs.getString("STATUS");
				if("T".equals(tf)){
					user.setStatus(true);
				}
				else{
					user.setStatus(false);
				}
				user.setRole(role);
				user.setPower(power);
			}
		}catch (Exception e) {
			System.out.println("根据用户名返回User对象，连接数据库出错!!");
		}finally{
			dbu.closeConnection(conn, pstat, rs);
		}
		return user;
	}

	public Page<User> getByRole(int pageNo, int pageSize, Role role) {
		Page<User> page = new Page<User>(pageNo, pageSize);
		int totalCount = 0;
		List<User> list = new ArrayList<User>();
		try {
			conn = dbu.getConnection();
			String sql = "select count(*) from t_user where roleid=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, role.getId());
			rs = pstat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select u.*,rownum rn from t_user u where rownum<=? and roleid=?) where rn>=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, page.getPageNo() * pageSize);
			pstat.setInt(2, role.getId());
			pstat.setInt(3, (page.getPageNo() - 1) * pageSize + 1);
			rs = pstat.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setUsername(rs.getString("USERNAME"));
				user.setPassword(rs.getString("PASSWORD"));
				user.setRole(role);
				if("T".equals(rs.getString("STATUS"))){
					user.setStatus(true);
				}else{
					user.setStatus(false);
				}
				Power power=new Power();
				power=powerdaoimpl.getPowerById(rs.getInt("POWERID"));
				user.setPower(power);
				list.add(user);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return page;
	}

	public Page<User> getByStatus(int pageNo, int pageSize, boolean status) {
		Page<User> page = new Page<User>(pageNo, pageSize);
		int totalCount = 0;
		List<User> list = new ArrayList<User>();
		String str="";
		if(status){
			str="T";
		}else{
			str="F";
		}
		try {
			conn = dbu.getConnection();
			String sql = "select count(*) from t_user where status=?";
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, str);
			rs = pstat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select u.*,rownum rn from t_user u where rownum<=? and status=?) where rn>=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, page.getPageNo() * pageSize);
			pstat.setString(2, str);
			pstat.setInt(3, (page.getPageNo() - 1) * pageSize + 1);
			rs = pstat.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setUsername(rs.getString("USERNAME"));
				user.setPassword(rs.getString("PASSWORD"));
				Role role=new Role();
				role=roledaoimpl.getById(rs.getInt("ROLEID"));
				user.setRole(role);
				if("T".equals(rs.getString("STATUS"))){
					user.setStatus(true);
				}else{
					user.setStatus(false);
				}
				Power power=new Power();
				power=powerdaoimpl.getPowerById(rs.getInt("POWERID"));
				user.setPower(power);
				list.add(user);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return page;
	}

	public boolean updateUser(User user) {
		boolean flag=false;
		try{
			conn = dbu.getConnection();
			String sql="update t_user set roleid=?,status=?,powerid=? where username=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, user.getRole().getId());
			if(user.isStatus()){
				pstat.setString(2, "T");
			}else{
				pstat.setString(2, "F");
			}
			pstat.setInt(3, user.getPower().getId());
			pstat.setString(4, user.getUsername());
			pstat.executeUpdate();
			flag=true;
		}catch (Exception e) {
			System.out.println("根据用户名修改用户信息，连接数据库出错!!");
		}finally{
			dbu.closeConnection(conn, pstat, rs);
		}
		return flag;
	}

	public Page<User> getByPower(int pageNo, int pageSize, Power power) {
		Page<User> page = new Page<User>(pageNo, pageSize);
		int totalCount = 0;
		List<User> list = new ArrayList<User>();
		try {
			conn = dbu.getConnection();
			String sql = "select count(*) from t_user where powerid=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, power.getId());
			rs = pstat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select u.*,rownum rn from t_user u where rownum<=? and powerid=?) where rn>=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, page.getPageNo() * pageSize);
			pstat.setInt(2, power.getId());
			pstat.setInt(3, (page.getPageNo() - 1) * pageSize + 1);
			rs = pstat.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setUsername(rs.getString("USERNAME"));
				user.setPassword(rs.getString("PASSWORD"));
				Role role=new Role();
				role=roledaoimpl.getById(rs.getInt("ROLEID"));
				user.setRole(role);
				if("T".equals(rs.getString("STATUS"))){
					user.setStatus(true);
				}else{
					user.setStatus(false);
				}
				user.setPower(power);
				list.add(user);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return page;
	}

	public Page<User> getByName(int pageNo, int pageSize,String name) {
		Page<User> page = new Page<User>(pageNo, pageSize);
		int totalCount = 1;
		List<User> list = new ArrayList<User>();
		User user=new User();
		Role role=new Role();
		Power power=new Power();
		String sql="select t.username,t.password,t.status,t.roleid,r.name,t.powerid,p.name from t_user t,role r,t_power p where username='"+name+"' and t.powerid=p.id and t.roleid=r.id";
		try{
			conn=dbu.getConnection();
			pstat=conn.prepareStatement(sql);
			rs=pstat.executeQuery();
			if(rs.next()){
				role.setId(rs.getInt("ROLEID"));
				role.setName(rs.getString("NAME"));
				power.setId(rs.getInt("POWERID"));
				power.setName(rs.getString("NAME"));
				user.setUsername(rs.getString("USERNAME"));
				user.setPassword(rs.getString("PASSWORD"));
				String tf=rs.getString("STATUS");
				if("T".equals(tf)){
					user.setStatus(true);
				}
				else{
					user.setStatus(false);
				}
				user.setRole(role);
				user.setPower(power);
				list.add(user);
				page.setTotalCount(totalCount);
				page.setList(list);
			}
		}catch (Exception e) {
			System.out.println("根据用户名返回User对象，连接数据库出错!!");
		}finally{
			dbu.closeConnection(conn, pstat, rs);
		}
		return page;
	}

	public boolean updateUserPassword(User user) {
		boolean flag=false;
		try{
			conn = dbu.getConnection();
			String sql="update t_user set password=? where username=?";
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, user.getPassword());
			pstat.setString(2, user.getUsername());
			pstat.executeUpdate();
			flag=true;
		}catch (Exception e) {
			System.out.println("根据用户名修改密码，连接数据库出错!!");
		}finally{
			dbu.closeConnection(conn, pstat, rs);
		}
		return flag;
	}

}
